/*
 * Copyright (c) 2013, SRI International
 * All rights reserved.
 * Licensed under the The BSD 3-Clause License;
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at:
 * 
 * http://opensource.org/licenses/BSD-3-Clause
 * 
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 * 
 * Redistributions of source code must retain the above copyright
 * notice, this list of conditions and the following disclaimer.
 * 
 * Redistributions in binary form must reproduce the above copyright
 * notice, this list of conditions and the following disclaimer in the
 * documentation and/or other materials provided with the distribution.
 * 
 * Neither the name of the aic-web-praise nor the names of its
 * contributors may be used to endorse or promote products derived from
 * this software without specific prior written permission.
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
 * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
 * COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, 
 * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) 
 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, 
 * STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
 * OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package com.sri.ai.lpitools.cobra.server.data.implementations.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Logger;

/**
 * Implementation of Database class for SQLite databases.
 *
 * @author Eric Tsai
 */
public class SQLiteDatabase {

    private final static Logger log = Logger.getLogger(SQLiteDatabase.class.getName());

    private Connection connection;  // Connection to the database
    protected Statement stmt;
    protected String databaseName;
	
    public SQLiteDatabase(String databaseName, String username, String password)
            throws ClassNotFoundException {
    	init( databaseName, username, password);
    }

    private void init(String databaseName, String username, String password) throws ClassNotFoundException {
        this.databaseName = databaseName;
        loadDriver();
    }
    
    /**
     * Loads the appropriate driver for database.
     *
     * @throws ClassNotFoundException if driver is missing
     */
    @SuppressWarnings("rawtypes")
	public void loadDriver() throws ClassNotFoundException {
    	
    	String driverName = "org.sqlite.JDBC";
    	
        log.info("Attempting to load driver...");
        Class tmpClass = Class.forName(driverName);
        log.info("driver class: " + tmpClass);

        if (tmpClass == null) {
            log.info("Could not loader driver: " + driverName);
        } else {
            log.info("Driver successfully loaded");
        }
    }
    
    public void connectToDatabase() throws SQLException {
    	
        log.info("Attempting connection to database with url=" + generateDataBaseURL());

        connection = DriverManager.getConnection( generateDataBaseURL());
        log.info("Connection " + connection);
        stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,  ResultSet.CONCUR_READ_ONLY);
        connection.setAutoCommit(false);
    }
    
    public void close() throws SQLException {
    	if (connection != null && !connection.isClosed()) {
            log.info("Closing the connection to the database");
            connection.close();
        }
    }
    
    private String generateDataBaseURL() {
        //The JDBC URL format for SQLite Connector/J is as follows, with items in square brackets ([, ]) being optional:
        //jdbc:sqlite://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...
        return "jdbc:sqlite:" + databaseName;
    }

    public static String escapeSingleQuotes(String input) {
        if (input == null) {
            return null;
        }
        return input.replaceAll("'", "''");
    }
    
    public ResultSet executeQuery(String query) throws SQLException {
        ResultSet rs = null;

        try {
            rs = stmt.executeQuery(query);
            connection.commit();
        } catch (SQLException sqle) {
            throw sqle;
        }
        return rs;
    }
    
    public int executeUpdate(String command) throws SQLException {
        return stmt.executeUpdate(command);
    }
    
    public PreparedStatement prepareStatement (String sql) throws SQLException {
    	return connection.prepareStatement(sql);
    }

}
